#
# import time
# import pymysql
#
# db = pymysql.connect(
#     host = 'localhost',
#     port = 3306,
#     database = '学生库',
#     user = 'root',
#     password = '123456',
#     charset = 'utf8'
# )
#
# # 编程实现：循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
# # create   table  person(
# # 	username  varchar(20),
# # 	age   int,
# # 	sex 	  char(4),
# # 	high  int
# # )
#
# cursor = db.cursor()
# sql = "create   table  person(username  varchar(20),age int,sex char(4),high  int)"
# date = cursor.execute(sql)
# print("完成创建")
# db.commit()
# cursor.close()
# db.close()
# cursor = db.cursor()
# sql = "INSERT INTO person (username,age,sex,high) VALUES (%s, %s, %s, %s)"
# for i in range(1,11):
#     username = input("请输入名称：")
#     age = input("请输入年龄：")
#     sex = input("请输入性别：")
#     high = input("请输入身高：")
#
#     param = (username, age, sex, high)
#     cursor.execute(sql, param)
# print("完成创建")
#
# db.commit()
# cursor.close()
# db.close()


# 编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
# CREATE TABLE `user_info` (
#   `id` int(11) ,
#   `idcard` varchar(50) ,
#   `username` varchar(50) ,
#   `realname` varchar(50) ,
#   `pwd` varchar(50) ,
#   `telphone` varchar(12) ,
#   `email` varchar(100) ,
#   `age` int(11) ,
#   `sex` varchar(20) ,
#   `address` varchar(200) ,
#   `hiredate` date ,
#   `sal` double(9,2) ,
#   `job` varchar(100) ,
#   `company` varchar(100)
# ) ;
#
#
# (实现之后思考如何提高写入速度：可以考虑executemany方法，嘿嘿！！)
# import pymysql
# import time
#
# db = pymysql.connect(
#     host = 'localhost',
#     port = 3306,
#     database = '学生库',
#     user = 'root',
#     password = '123456',
#     charset = 'utf8'
# )
# couser = db.cursor()
# sql = ("CREATE TABLE `user_info` ("
# "`id` int(11) ,"
# "`idcard` varchar(50) ,"
# "`username` varchar(50) ,"
# "`realname` varchar(50) ,"
# "`pwd` varchar(50) ,"
# "`telphone` varchar(12) ,"
# "`email` varchar(100) ,"
# "`age` int(11) ,"
# "`sex` varchar(20) ,"
# "`address` varchar(200) ,"
# "`hiredate` date ,"
# "`sal` double(9,2) ,"
# "`job` varchar(100) ,"
# "`company` varchar(100))")
# print(sql)
# couser.execute(sql)
# print('成功创建表`user_info`')
# db.commit()
# couser.close()
# db.close()
#
# import pymysql
# import datetime
# import openpyxl
# from faker import Faker
#
# def xlsx_1(s):
#         wb = openpyxl.Workbook()
#         ws = wb.active
#
#
#         headers = ['id', 'idcard', 'username', 'realname', 'pwd', 'telphone', 'email', 'age', 'sex', 'address']
#         ws.append(headers)
#
#         faker = Faker('zh_CN')
#         for i in range(1,2001):
#                 date = [
#                         i,
#                         faker.ssn(),
#                         faker.user_name(),
#                         faker.name(),
#                         faker.password(),
#                         faker.phone_number(),
#                         faker.email(),
#                         faker.random_int(18, 66),
#                         faker.random_element(elements=('男', '女')),
#                         faker.address()
#                         ]
#                 ws.append(date)
#
#         wb.save(s)
#
# def into_db(s):
#         connection = pymysql.connect(
#                 host='localhost',
#                 user='root',
#                 password='123456',
#                 database='学生库',
#                 charset='utf8mb4'
#         )
#         cursor = connection.cursor()
#
#         wb = openpyxl.load_workbook(s)
#         ws = wb.active
#         for row in ws.iter_rows(min_row=2, values_only= True):
#                 sql = '''insert into user_info values
#                          (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) '''
#
#                 cursor.execute(sql, row)
#         connection.commit()
#         cursor.close()
#         connection.close()
#
#
# s = input()
# s = s + '.xlsx'
# xlsx_1(s)
# into_db(s)